﻿using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.IO;
using System.Xml;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml.Linq;
using VAdvantage.Logging;
using VAdvantage.Utility;
using VAdvantage.Login;
using VAdvantage.DataBase;
using VAdvantage.Model;

namespace VAdvantage.Install
{
    public class Translation
    {
        /// <summary>
        /// Translation	 
        /// </summary>
        /// <param name="ctx">context</param>
        public Translation(Ctx ctx)
        {
            _ctx = ctx;
        }	//	Translation

        /**	DTD						*/
        public static String DTD = "<!DOCTYPE ViennaTrl PUBLIC \"-//Vienna, Inc.//DTD Vienna Translation 1.0//EN\" \"http://www.Viennasolutions.com\">";
        /**	XML Element Tag			*/
        public static String XML_TAG = "compiereTrl";

        public static String XML_TAG_Vienna = "ViennaTrl";
        /**	XML Attribute Table			*/
        public static String XML_ATTRIBUTE_TABLE = "table";
        /** XML Attribute Language		*/
        public static String XML_ATTRIBUTE_LANGUAGE = "language";

        /**	XML Row Tag					*/
        public static String XML_ROW_TAG = "row";
        /** XML Row Attribute ID		*/
        public static String XML_ROW_ATTRIBUTE_ID = "id";
        /** XML Row Attribute Translated	*/
        public static String XML_ROW_ATTRIBUTE_TRANSLATED = "trl";

        /**	XML Value Tag				*/
        public static String XML_VALUE_TAG = "value";
        /** XML Value Column			*/
        public static String XML_VALUE_ATTRIBUTE_COLUMN = "column";
        /** XML Value Original			*/
        public static String XML_VALUE_ATTRIBUTE_ORIGINAL = "original";

        /**	Table is centrally maintained	*/
        private bool _IsCentrallyMaintained = false;
        /**	Logger						*/
        private VLogger log = VLogger.GetVLogger(typeof(Translation).FullName);// getClass());
        /** Context						*/
        private Ctx _ctx = null;

        /// <summary>
        /// 	Import Translation.
        //Uses TranslationHandler to update translation         
        /// </summary>
        /// <param name="directory">file Directory</param>
        /// <param name="AD_Client_ID">only certain client if id >= 0</param>
        /// <param name="AD_Language">language</param>
        /// <param name="Trl_Table">table</param>
        /// <returns>status Message</returns>

        public String ImportTrl(Stream stream, String directory, int AD_Client_ID, String AD_Language, String Trl_Table)
        {
            String fileName = directory + "\\" + Trl_Table + "_" + AD_Language + ".xml";
            log.Info(fileName);
            try
            {
                FileInfo inn = new FileInfo(fileName);
                TranslationHandler handler = new TranslationHandler(AD_Client_ID);
                XmlReaderSettings factory = new XmlReaderSettings();
                factory.IgnoreComments = true;
                factory.IgnoreProcessingInstructions = true;
                factory.IgnoreWhitespace = true;
                FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Read, FileShare.Read);
                XmlReader pp1 = XmlReader.Create(fs);
                //System.Threading.ThreadPool.QueueUserWorkItem(delegate
                //{
                    using (var xmlReader = XmlReader.Create(stream))
                    {
                        while (xmlReader.Read())
                        {
                            switch (xmlReader.NodeType)
                            {
                                case XmlNodeType.Element:
                                    bool isEmptyElement = false;
                                    isEmptyElement = xmlReader.IsEmptyElement;
                                    string strURI = xmlReader.NamespaceURI;
                                    string strName = xmlReader.Name;
                                    string strlocal = xmlReader.LocalName;
                                    List<string> att = new List<string>();
                                    if (xmlReader.HasAttributes)
                                    {
                                        for (int i = 0; i < xmlReader.AttributeCount; i++)
                                        {
                                            att.Add(xmlReader.GetAttribute(i));
                                        }
                                    }
                                    handler.StartElement(strURI, strlocal, strName, att);
                                    if (strName == "value")
                                    {
                                        if (att[1] != "")
                                        {
                                            char[] ch = att[1].ToCharArray();
                                            handler.Characters(ch);
                                        }

                                    }
                                    break;
                                case XmlNodeType.EndElement:
                                    string URI = xmlReader.NamespaceURI;
                                    string Name = xmlReader.Name;
                                    handler.EndElement(URI, "", Name);
                                    break;
                            }
                        }
                    }

                    #region CommentedCode

                    //XmlReader parser = XmlReader.Create(new StringReader(inn.FullName), factory);
                    //Uri u = new Uri(fileName, UriKind.RelativeOrAbsolute);
                    //XmlReader pp = XmlReader.Create(u.AbsoluteUri);
                    //XmlReader parser1 = XmlReader.Create(stream);
                    //for (int count = 0; count < parser.AttributeCount; count++)
                    //{
                    //    switch (parser.NodeType)
                    //    {
                    //        case XmlNodeType.Element:
                    //            bool isEmptyElement = false;
                    //            isEmptyElement = parser.IsEmptyElement;
                    //            string strURI = parser.NamespaceURI;
                    //            string strName = parser.Name;
                    //            string strlocal = parser.LocalName;
                    //            List<string> att = new List<string>();
                    //            if (parser.HasAttributes)
                    //            {
                    //                for (int i = 0; i < parser.AttributeCount; i++)
                    //                {
                    //                    att.Add(parser.GetAttribute(i));
                    //                }
                    //            }
                    //            handler.StartElement(strURI, strlocal, strName, att);
                    //            if (strName == "value")
                    //            {
                    //                if (att[1] != "")
                    //                {
                    //                    char[] ch = att[1].ToCharArray();
                    //                    handler.Characters(ch);
                    //                }

                    //            }
                    //            break;
                    //        case XmlNodeType.EndElement:
                    //            string URI = parser.NamespaceURI;
                    //            string Name = parser.Name;
                    //            handler.EndElement(URI, "", Name);
                    //            break;
                    //    }
                    //}
                    #endregion
                    log.Info("Updated=" + handler.GetUpdateCount());
                    //return Msg.GetMsg(_ctx, "Updated") + "=" + handler.GetUpdateCount();
                //});
                return Msg.GetMsg(_ctx, "Updated") + "=" + handler.GetUpdateCount();


            }
            catch (Exception e)
            {
                log.Log(Level.SEVERE, "importTrl", e);
                return e.ToString();
            }

        }	//	importTrl


        /// <summary>
        /// Import Translation
        /// </summary>
        /// <param name="directory">file directory</param>
        /// <param name="AD_Client_ID">only certain client if id >= 0</param>
        /// <param name="AD_Language">language</param>
        /// <param name="Trl_Table">table</param>
        /// <returns>stauts Message </returns>
        public String ExportTrl(int AD_Client_ID, String AD_Language, String Trl_Table, SaveFileDialog chooser)
        {
            //  String fileName = directory + "\\" + Trl_Table + "_" + AD_Language + ".xml";
            bool isBaseLanguage = Language.IsBaseLanguage(AD_Language);
            String tableName = Trl_Table;
            int pos = tableName.IndexOf("_Trl");
            String Base_Table = Trl_Table.Substring(0, pos);
            if (isBaseLanguage)
                tableName = Base_Table;
            String keyColumn = Base_Table + "_ID";
            //String[] trlColumns = GetTrlColumns(Base_Table);
            //GetTrlColumns
            _IsCentrallyMaintained = false;
            SqlParameter[] param = new SqlParameter[1];
            SqlParameter[] param1 = new SqlParameter[1];
            String sql1 = "SELECT TableName FROM AD_Table t"
                + " INNER JOIN AD_Column c ON (c.AD_Table_ID=t.AD_Table_ID AND c.ColumnName='IsCentrallyMaintained') "
                + "WHERE t.TableName=@param AND c.IsActive='Y'";

            String sql2 = "SELECT ColumnName "
               + "FROM AD_Column c"
               + " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID) "
               + "WHERE t.TableName=@param1"
               + " AND c.AD_Reference_ID IN (10,14) "
               + "ORDER BY IsMandatory DESC, ColumnName";
            List<String> list = new List<String>();


            IDataReader idr = null;
            IDataReader idr1 = null;
            try
            {

                param[0] = new SqlParameter("@param", Base_Table);
                param1[0] = new SqlParameter("@param1", Base_Table + "_Trl");
                System.Threading.ThreadPool.QueueUserWorkItem(delegate
                {

                    idr = DB.ExecuteReader(sql1, param, null);
                    idr1 = DB.ExecuteReader(sql2, param1, null);
                    System.Windows.Deployment.Current.Dispatcher.BeginInvoke(delegate
                    {
                        TrlColumns(idr);
                        TrlColumnName(idr1, list);
                        String[] trlColumns = list.ToArray();
                        //GetTrlColumns
                        StringBuilder sql = null;
                        try
                        {

                            XDocument xmlDoc = new XDocument();
                            XDeclaration dec = new XDeclaration("1.0", "UTF-8", "no");
                            xmlDoc.Declaration = dec;
                            XComment xmlcmnt = new XComment(DTD);
                            xmlDoc.AddFirst(xmlcmnt);
                            XElement document = new XElement(XML_TAG_Vienna);
                            document.SetAttributeValue(XML_ATTRIBUTE_LANGUAGE, AD_Language);
                            document.SetAttributeValue(XML_ATTRIBUTE_TABLE, Base_Table);
                            xmlDoc.Add(document);

                            sql = new StringBuilder("SELECT ");
                            if (isBaseLanguage)
                                sql.Append("'Y',");							//	1
                            else
                                sql.Append("t.IsTranslated,");
                            sql.Append("t.").Append(keyColumn);				//	2
                            //
                            for (int i = 0; i < trlColumns.Length; i++)
                                sql.Append(", t.").Append(trlColumns[i])
                                    .Append(",o.").Append(trlColumns[i]).Append(" AS ").Append(trlColumns[i]).Append("O");
                            //
                            sql.Append(" FROM ").Append(tableName).Append(" t")
                                .Append(" INNER JOIN ").Append(Base_Table)
                                .Append(" o ON (t.").Append(keyColumn).Append("=o.").Append(keyColumn).Append(")");
                            bool haveWhere = false;
                            if (!isBaseLanguage)
                            {
                                sql.Append(" WHERE t.AD_Language=@param");
                                haveWhere = true;
                            }
                            if (_IsCentrallyMaintained)
                            {
                                sql.Append(haveWhere ? " AND " : " WHERE ").Append("o.IsCentrallyMaintained='N'");
                                haveWhere = true;
                            }
                            if (AD_Client_ID >= 0)
                                sql.Append(haveWhere ? " AND " : " WHERE ").Append("o.AD_Client_ID=").Append(AD_Client_ID);
                            sql.Append(" ORDER BY t.").Append(keyColumn);

                            //SqlParameter[] param1 = null;
                            IDataReader idr2 = null;
                            if (!isBaseLanguage)
                            {
                                param = new SqlParameter[1];
                                param[0] = new SqlParameter("@param", AD_Language);
                            }
                            System.Threading.ThreadPool.QueueUserWorkItem(delegate
                            {
                                if (!isBaseLanguage)
                                {
                                    idr2 = DB.ExecuteReader(sql.ToString(), param);
                                }
                                else
                                {
                                    idr2 = DB.ExecuteReader(sql.ToString());
                                }

                                System.Windows.Deployment.Current.Dispatcher.BeginInvoke(() => FileContent(idr2, Trl_Table, trlColumns, xmlDoc, document, chooser));
                                //  idr.Close();

                            });




                        }

                        catch (Exception e)
                        {
                            log.Log(Level.SEVERE, "", e);
                        }
                    });
                });


            }
            catch { }
            return "";
        }

        private void FileContent(IDataReader idr, String Trl_Table, String[] trlColumns, XDocument xmlDoc, XElement document, SaveFileDialog chooser)
        {

            int rows = 0;

            while (idr.Read())
            {
                XElement row = new XElement(XML_TAG_Vienna);
                row.SetAttributeValue(XML_ROW_ATTRIBUTE_ID, Util.GetValueOfInt(idr[1]));
                row.SetAttributeValue(XML_ROW_ATTRIBUTE_TRANSLATED, Util.GetValueOfString(idr[0]));
                for (int i = 0; i < trlColumns.Length; i++)
                {

                    XElement value = new XElement(XML_TAG_Vienna);
                    value.SetAttributeValue(XML_VALUE_ATTRIBUTE_COLUMN, trlColumns[i]);
                    String origString = Util.GetValueOfString(idr[(trlColumns[i] + "O")]);			//	Original Value
                    if (origString == null)
                        origString = "";
                    String valueString = Util.GetValueOfString(idr[trlColumns[i]]);				//	Value
                    if (valueString == null)
                        valueString = "";

                    value.SetAttributeValue(XML_VALUE_ATTRIBUTE_ORIGINAL, origString);
                    row.Add(value);

                }

                document.Add(row);
                rows++;
            }
            idr.Close();
            log.Info("Records=" + rows
                + ", DTD=" + document.GetType()
                + " - " + Trl_Table);

            //chooser.


            //byte[] bytes = chooser.OpenFile().GetAllBytes();
            //MemoryStream str = new MemoryStream(bytes);
            //xmlDoc.Save(str);

            using (StreamWriter sw = new StreamWriter(chooser.OpenFile()))
            {
                sw.Write(document);
            }


        }	//	exportTrl

        private void TrlColumnName(IDataReader idr, List<String> list)
        {

            while (idr.Read())
            {
                String s = Util.GetValueOfString(idr[0]);// rs.getString(1);		
                list.Add(s);
            }
            idr.Close();
        }

        private void TrlColumns(IDataReader idr)
        {

            if (idr.Read())
                _IsCentrallyMaintained = true;
            idr.Close();
        }

        //	getTrlColumns

        MLanguage language = null;
        /// <summary>
        /// Validate Language.	 * 	
        //  - Check if AD_Language record exists
        // - Check Trl table records	 
        /// </summary>
        /// <param name="AD_Language">language</param>
        /// <returns>if validated - or error message</returns>
        public String ValidateLanguage(String AD_Language)
        {
            String sql = "SELECT * "
                + "FROM AD_Language "
                + "WHERE AD_Language='" + AD_Language + "'";


            //System.Threading.ThreadPool.QueueUserWorkItem(delegate
            //   {
                   DataSet ds = DB.ExecuteDataset(sql, null);
                   FillData(ds, AD_Language);

               //});



            //  SqlParameter[] param = new SqlParameter[1];



            //try
            //{
            //    param[0] = new SqlParameter("@param", AD_Language);
            //    System.Threading.ThreadPool.QueueUserWorkItem(delegate
            //    {
            //        idr = DB.ExecuteReader(sql, param, null);
            //        System.Windows.Deployment.Current.Dispatcher.BeginInvoke(() => FillData(idr, AD_Language));
            //        idr.Close();
            //    });


            //}
            //catch (Exception e)
            //{
            //    idr.Close();
            //    log.Log(Level.SEVERE, sql, e);
            //    return e.ToString();
            //}


            return "";
        }//	validateLanguage

        private String FillData(DataSet ds, String AD_Language)
        {
            //if (idr.Read())
            //    language = new MLanguage(_ctx, idr, null);
            //idr.Close();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                language = new MLanguage(_ctx, Convert.ToInt32(ds.Tables[0].Rows[0]["AD_Language_ID"]), null);
                //	No AD_Language Record
                if (language == null)
                {
                    log.Log(Level.SEVERE, "Language does not exist: " + AD_Language);
                    return "Language does not exist: " + AD_Language;
                }
                //	Language exists
                if (language.IsActive())
                {
                    if (language.IsBaseLanguage())
                        return "";
                }
                else
                {
                    log.Log(Level.SEVERE, "Language not active or not system language: " + AD_Language);
                    return "Language not active or not system language: " + AD_Language;
                }

                //	Validate Translation
                log.Info("Start Validating ... " + language);
                language.Maintain(true);
            }
            return "";

        }


        /// <summary>
        ///process
        /// </summary>
        /// <param name="directory">directory</param>
        /// <param name="AD_Language">language</param>
        /// <param name="mode">mode</param>
        private void Process(String directory, String AD_Language, String mode)
        {
            //DirectoryInfo dir = new DirectoryInfo(directory);
            //if (!dir.Exists)
            //{
            //    dir.Create();
            //}
            //dir = new DirectoryInfo(directory);
            //if (!dir.Exists)
            //{
            //    return;
            //}
            String sql = "SELECT Name, TableName "
                + "FROM AD_Table "
                + "WHERE TableName LIKE '%_Trl' "
                + "ORDER BY 1";
            List<String> trlTables = new List<String>();
            IDataReader idr = null;
            try
            {
                idr = DB.ExecuteReader(sql);
                while (idr.Read())
                    trlTables.Add(Util.GetValueOfString(idr[1]));// rs.getString(2));
                idr.Close();
            }
            catch (Exception e)
            {
                idr.Close();
                log.Log(Level.SEVERE, sql, e);
            }

            for (int i = 0; i < trlTables.Count; i++)
            {
                String table = (String)trlTables[i];
                Stream stream = null;
                if (mode.StartsWith("i"))
                    ImportTrl(stream, directory, -1, AD_Language, table);
                else
                { }
                //ExportTrl(directory, -1, AD_Language, table);
            }
        }

    }
}
